In this article I am trying explain the concept of JOINs using SQL Server.
Definition:
JOIN clause is used to combine records from two or more tables in a database, based on a common field between them. It creates a set that can be saved as a table or use as it is.
Types of JOIN:
There are four types of JOIN:
1-INNER JOIN
2-OUTER JOIN
3-SELF JOIN
4-CROSS JOIN
1-INNER JOIN:
It is most common join operation used in application and can be regarded as the default join type.The INNER JOIN creates a result table by combining column value of two tables based upon the join predicate.
The INNER JOIN is further classified into two parts EQUI JOIN and NATURAL JOIN.
EQUI JOIN is used to display all matching records from joined table and is also display duplicate values.
Syntax for EQUI JOIN:
SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.ColumnName=t2.ColumnName
NATURAL Join is same as EQUI join but is does not show duplicate values.
Syntax for NATURAL JOIN :
SELECT * FROM table1 t1 NATURAL JOIN table2 t2 ON t1.ColumnName=t2.ColumnName
2-OUTER JOIN:
It does not require that each record in two joined tables to have matching records.
The Join table retains each record – even if no other matching record exists.
The outer join is further divided in to three parts LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.
LEFT OUTER JOIN return all rows of left table and matching records of right table.
Syntax of LEFT OUTER JOIN:
SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.ColumnName=t2.ColumnName
RIGHT OUTER JOIN returns all rows of right side table and matching rows of left side table.
Syntax of RIGHT OUTER JOIN:
SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.ColumnName=t2.ColumnNameFULL OUTER JOIN returns all rows of both tables.
Syntax of FULL OUTER JOIN:
SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.ColumnName=t2.ColumnName3-SELF JOIN:
A SELF JOIN is joining table itself. It is used to retrieve record having some relation or similarity with other records.
Syntax for SELF JOIN:
select t2.ColumnName,t2.ColumnName as 'NewColumnName'
from table t1
INNER JOIN table t2
on t1.ColumnName=t2.ColumnName
4-CROSS JOIN:
It returns the Cartesian product of rows from tables in the join. The size of a Cartesian product is number of row in first table multiplied by number of rows in second table.
Syntax for CROSS JOIN:
SELECT * FROM table1 CROSS JOIN table2
Leave Comment